package db.document;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import o.财务单据类型;
import db.Database;
import db.history.SystemLogDB;
import po.AccountBookPo;
import po.AccountItemPo;
import po.DocumentPo;
import po.Po;
import po.SystemLogPo;
import po.收付款单po;

public class AccountInAndOutDocumentDB extends DocumentDB{
	
	  public AccountInAndOutDocumentDB(AccountBookPo ab) throws SQLException {
		super(ab);
		
		String in_item="CREATE TABLE IF NOT EXISTS `seac2`.`"+ab.getName()+"_pay_item` (`f_key` int(8) NOT NULL, `f_id` int(4) NOT NULL AUTO_INCREMENT, `f_account` varchar(20) NOT NULL, `f_money` BIGINT(0) NOT NULL, `f_remark` varchar(50) NOT NULL,PRIMARY KEY (`f_id`), UNIQUE KEY `f_id` (`f_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8";
		String createT_account_In="CREATE TABLE IF NOT EXISTS `seac2`.`"+ab.getName()+"_Pay_In_Out` (`f_id` varchar(20) NOT NULL,`f_client` varchar(20) NOT NULL,`f_operator` varchar(20) NOT NULL, `f_item` int(4) NOT NULL AUTO_INCREMENT, `f_total` BIGINT(0) NOT NULL,`f_isApproved` int(1) NOT NULL DEFAULT '0', `f_date` date NOT NULL,`f_account_type` int(1) NOT NULL,PRIMARY KEY `f_item` (`f_item`),UNIQUE KEY `f_item` (`f_item`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8";
		Connection c = Database.getConnection();
		Statement s = c.createStatement();
		s.execute(createT_account_In);
		s.execute(in_item);
		s.close();
}

	

	public Po approve(DocumentPo po) {
		String id=po.get单据编号();String client = null;long total=0;
		Po m=new Po();int key=0;
		Connection c=Database.getConnection();
		Statement s;
		try {
			s = c.createStatement();
			ResultSet rs5=s.executeQuery("select f_isApproved from  "+book.getName()+"_Pay_In_Out where f_id='"+id+"'");
			while(rs5.next()){
				if(rs5.getInt(1)==1){
					m.failed("该单据已被审批！");
					return m;
				}
			}
			s.executeUpdate("update "+book.getName()+"_Pay_In_Out set f_isApproved='"+1+"' where f_id='"+id+"';");
			ResultSet rs=s.executeQuery("select f_client,f_total,f_item from "+book.getName()+"_Pay_In_Out where f_id='"+id+"';");
			while(rs.next()){
			 client=rs.getString("f_client");
			 total=rs.getLong("f_total");
			 key=rs.getInt("f_item");
			}
			
			ResultSet rs2=s.executeQuery("select * from "+book.getName()+"_pay_item where f_key='"+key+"'");
			List<AccountItemPo> item=new ArrayList<AccountItemPo>();
			while(rs2.next()){
				String account=rs2.getString(3);
				long money=rs2.getLong(4);
				String info=rs2.getString(5);
				item.add(new AccountItemPo(account,money,info));
			}
			
			if(id.contains("SKD")){                                        //收款单审批后，修改客人应fu；付款单审批后，修改客人应shou;//修改帐户金额
				s.execute("update "+book.getName()+"_client set yingshou=(yingshou-"+total+") where name='"+client+"'");
				for(int i=0;i<item.size();i++){
				s.execute("update "+book.getName()+"_account set money=(money+"+item.get(i).get转账金额()+") where name='"+item.get(i).get银行账户()+"'");
				}
			}else if(id.contains("FKD")){
				s.execute("update "+book.getName()+"_client set yingfu=(yingfu-"+total+") where name='"+client+"'");
				for(int i=0;i<item.size();i++){
					s.execute("update "+book.getName()+"_account set money=(money-"+item.get(i).get转账金额()+") where name='"+item.get(i).get银行账户()+"'");
					}
			}
			
			s.close();
			m.successed();
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		return m;
	}
	
/*	public Po update(收付款单po po) {
		String id=po.get单据编号();
		String client=po.get客户();
		String operator=po.get操作员();
		
		Po m=new Po();
		Connection c=Database.getConnection();
		Statement s;
		try {
			s = c.createStatement();
			s.executeUpdate("update "+book.getName()+"_Pay_In_Out set f_isApproved='"+1+"'where f_id='"+id+"';");
			s.close();
			m.successed();
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		return m;
	}
*/
	public List<DocumentPo> getUnapproval() {
		List<DocumentPo> list=new ArrayList<DocumentPo>();
		Connection c=Database.getConnection();
		Statement s;Statement s2;
		try {
			s = c.createStatement();
			ResultSet rs=s.executeQuery("select * from "+book.getName()+"_Pay_In_Out where f_isApproved='"+0+"'");
			while(rs.next()){
				收付款单po po=new 收付款单po();
				po.set单据编号(rs.getString(1));
				po.set客户(rs.getString(2));
				po.set操作员(rs.getString(3));
				po.set总额汇总(rs.getInt(5));
				po.set单据类型(rs.getInt(8)==0? 财务单据类型.收款单:财务单据类型.付款单);
				List<AccountItemPo> item=new ArrayList<AccountItemPo>();
				int key=rs.getInt(4);
				s2=c.createStatement();
				ResultSet rs2=s2.executeQuery("select * from "+book.getName()+"_pay_item where f_key='"+key+"'");
				while(rs2.next()){
					String account=rs2.getString(3);
					long money=rs2.getLong(4);
					String info=rs2.getString(5);
					item.add(new AccountItemPo(account,money,info));
				}
				s2.close();
				po.set转账列表(item);
				list.add(po);
		}
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<DocumentPo> get(DocumentPo from, DocumentPo to) {
		List<DocumentPo> list=new ArrayList<DocumentPo>();
		
		String client=from.get客户();
		String from_date;String to_date;
		
		from_date=from.getDate()==null? null:new SimpleDateFormat("yyyy-MM-dd").format(from.getDate());
		to_date=to.getDate()==null?null:new SimpleDateFormat("yyyy-MM-dd").format(to.getDate());
		String query_client=client==null? "":"and f_client like '%"+client+"%'";
		String query_from=from_date==null? "":"and f_date>='"+from_date+"'";
		String query_to=to_date==null? "":"and f_date<='"+to_date+"'";
		
		int type=from.isType()? 1:0;      //收款单传递type=false给我
		Connection c=Database.getConnection();
		Statement s;Statement s2;ResultSet rs = null;
		try {
			s = c.createStatement();
			
			    rs=s.executeQuery("select * from "+book.getName()+"_Pay_In_Out where f_isApproved='"+1+"'"+query_client+query_from+query_to+ "and f_account_type='"+type+"'");//+"and f_date>='"+from_date+"'"+"and f_date<='"+to_date+"'"
			while(rs.next()){
				收付款单po po=new 收付款单po();
				po.set单据编号(rs.getString(1));
				po.set客户(rs.getString(2));
				po.set业务员(rs.getString("f_operator"));
				po.set操作员(rs.getString(3));
				po.set总额汇总(rs.getInt(5));
				po.set单据类型(rs.getInt("f_account_type")==0? 财务单据类型.收款单:财务单据类型.付款单);
				List<AccountItemPo> item=new ArrayList<AccountItemPo>();
				int key=rs.getInt(4);
				s2=c.createStatement();
				ResultSet rs2=s2.executeQuery("select * from "+book.getName()+"_pay_item where f_key='"+key+"'");
				while(rs2.next()){
					String account=rs2.getString(3);
					long money=rs2.getLong(4);
					String info=rs2.getString(5);
					item.add(new AccountItemPo(account,money,info));
				}
				s2.close();
				po.set转账列表(item);
				list.add(po);
		}
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public Po add(DocumentPo po) {
		List<AccountItemPo> list=((收付款单po) po).get转账列表();
		String date=new SimpleDateFormat("yyyy-MM-dd").format(new Date());int type;String r_id;
		int total_num=0;
		Po m=new Po();
		if(((收付款单po) po).get客户()==null){
			m.failed("请完整单据！");
			return m;
		}
		Connection c=Database.getConnection();
		Statement s;
		try {
			int key=0;
			s = c.createStatement();
			ResultSet rs=s.executeQuery("select * from "+book.getName()+"_Pay_In_Out");
			while(rs.next()){
				total_num++;
			}
			if(((收付款单po) po).get单据类型()==财务单据类型.收款单){
				type=0;
				r_id=getDocumentId("SKD",total_num);
			}else{
				type=1;
				r_id=getDocumentId("FKD",total_num);
			}
			s.executeUpdate("insert into "+book.getName()+"_Pay_In_Out(f_id,f_account_type,f_client,f_operator,f_total,f_date) values('"+r_id+"','"+type+"','"+((收付款单po) po).get客户()+"','"+((收付款单po) po).get操作员()+"','"+((收付款单po) po).get总额汇总()+"','"+date+"');");
			ResultSet r=s.executeQuery("select f_item from "+book.getName()+"_Pay_In_Out where f_id='"+r_id+"'");
			while(r.next()){
		    key=r.getInt("f_item");
			}
			for(int i=0;i<list.size();i++){
			s.executeUpdate("insert into "+book.getName()+"_pay_item(f_key,f_account,f_money,f_remark) values('"+key+"','"+list.get(i).get银行账户()+"','"+list.get(i).get转账金额()+"','"+list.get(i).get备注()+"');");
			}
			s.close();
			m.successed();
			SystemLogDB log=new SystemLogDB();
			SystemLogDB.iniSystemLogDB();
			log.add(new SystemLogPo(po.get操作员(),"add",r_id));
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		return m;
	}



	@Override
	public Po update(DocumentPo document) {
		Po m=new Po();String doc_id=((收付款单po)document).get单据编号();
		String client=((收付款单po)document).get客户();
		String operator=((收付款单po)document).get操作员();
		List<AccountItemPo> list=((收付款单po) document).get转账列表();
		int key=0;
		Connection c=Database.getConnection();
		Statement s;
		try {
			s=c.createStatement();
			if(client!=null){
				s.addBatch("update "+book.getName()+"_Pay_In_Out set f_client='"+client+"' where f_id='"+doc_id+"'");
			}
			if(operator!=null){
				s.addBatch("update "+book.getName()+"_Pay_In_Out set f_operator='"+client+"' where f_id='"+doc_id+"'");
			}
			ResultSet rs=s.executeQuery("select f_item from "+book.getName()+"_Pay_In_Out where f_id='"+doc_id+"'");
			while(rs.next()){
				key=rs.getInt(1);
			}
			//更新列表
			s.addBatch("delete  from "+book.getName()+"_pay_item where f_key='"+key+"'");
			for(int i=0;i<list.size();i++){
				s.addBatch("insert into "+book.getName()+"_pay_item(f_key,f_account,f_money,f_remark) values('"+key+"','"+list.get(i).get银行账户()+"','"+list.get(i).get转账金额()+"','"+list.get(i).get备注()+"');");
		    }
			s.executeBatch();
			s.close();
			m.successed();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		
		
		
		return m;
	}



	

	

}
